﻿CREATE PROCEDURE [dbo].[Forum_ClarifyNote_Post]
	@threadNoteId BIGINT,
	@value NVARCHAR(MAX),
	@userId UNIQUEIDENTIFIER

AS
BEGIN
	DECLARE @threadId BIGINT
	SELECT @threadId = [ThreadId]
	FROM [dbo].[forum_ThreadNote]
	WHERE [ThreadNoteId] = @threadNoteId

	IF @threadId IS NULL
		RETURN

	DECLARE @clarifyNoteId BIGINT
	SELECT @clarifyNoteId = [ClarifyNoteId] FROM [dbo].[forum_ClarifyNote] WHERE [CreatedBy] = @userId AND [ThreadNoteId] = @threadNoteId AND [Text] = @value

	DECLARE @isNew BIT = 0

	IF @clarifyNoteId IS NULL
	BEGIN
		BEGIN TRAN
			DECLARE @order INT
			SELECT @order = MAX([Order]) FROM [dbo].[forum_ClarifyNote] WHERE [ThreadNoteId] = @threadNoteId

			INSERT INTO [dbo].[forum_ClarifyNote]
								 ([ThreadNoteId]
								 ,[Text]
								 ,[Order]
								 ,[CreatedBy])
					 VALUES
								 (@threadNoteId
								 ,@value
								 ,CASE WHEN @order IS NULL THEN 1 ELSE @order + 1 END
								 ,@userId)

			SET @clarifyNoteId = SCOPE_IDENTITY()


			UPDATE [dbo].[forum_Thread]
				 SET [UpdateBy] = @userId
						,[UpdateDate] = GETUTCDATE()
			WHERE [ThreadId] = @threadId

			SET @isNew = 1
		COMMIT TRAN
	END

	DECLARE @sectionHashTag NVARCHAR(64)

	SELECT @sectionHashTag =[HashTag]
	FROM [dbo].[forum_Section]
	WHERE [SectionId] = (SELECT TOP 1 [SectionId] FROM [forum_Thread] WHERE [ThreadId] = @threadId)

	SELECT c.[ClarifyNoteId]
      ,c.[Text]
      ,c.[CreatedDate]
			,u.[NickName]
			,u.[EmailSh1]
			,@sectionHashTag AS [SectionHashTag]
			,@threadId AS [ThreadId]
			,@isNew AS [IsNew]
  FROM [dbo].[forum_ClarifyNote] AS c
	INNER JOIN [dbo].[user_User] AS u ON u.[UserId] = c.[CreatedBy]
	WHERE c.[ClarifyNoteId] = @clarifyNoteId

END